package com.utility;

import java.sql.*;

import javax.sql.*;
import java.util.*;
import com.bean.Mobile;

public class MobileDAO 
{
	private Connection con;
	private String dbURL = "jdbc:mysql://localhost:3306/";
	//private String dbURL =  "jdbc:mysql://mysql-MyMobileShop.jelastic.servint.net/";
	private String dbName = "mobileshop";
	private String username = "root";
	//private String password = "WGRdohZyAn";
	private String password = "qwert";
	
	public void connect()
	{
		try 
		{
			Class.forName("com.mysql.jdbc.Driver");
		} 
		catch (ClassNotFoundException e) 
		{
			System.out.println("[#]Error in loading Driver");
			e.printStackTrace();
		}
		
		try 
		{
			con = DriverManager.getConnection(dbURL+dbName,username,password);
		} 
		catch (SQLException e) 
		{
			System.out.println("[#]Error in getting connection");
			e.printStackTrace();
		}
		
	}
	
	public ArrayList get(String name)
	{
		ArrayList<Mobile> mobilesList = new ArrayList<Mobile>();
		ResultSet result;
		PreparedStatement preparedStatement;
		String query = "SELECT * FROM mobiles WHERE model LIKE ?";
		
		connect();
		
		try 
		{
			preparedStatement = con.prepareStatement(query);
			preparedStatement.setString(1,"%" + name + "%");
			result = preparedStatement.executeQuery();
		} 
		catch (SQLException e) 
		{
			System.out.println("[#]Error in getting Result 1");
			//e.printStackTrace();
			return null;
		}
		
		try 
		{
			while(result.next())
			{
				int id = result.getInt(1);
				String model = result.getString(2);
				String company = result.getString(3);
				String camera = result.getString(4);
				int price = result.getInt(5);
				
				Mobile mobile = new Mobile(id,model,company,camera,price);
				
				mobilesList.add(mobile);
			}
		} 
		catch (SQLException e) 
		{
			System.out.println("[#]Error in getting Result 2");
			//e.printStackTrace();
			return null;
		}
		
		disconnect();
	
		return mobilesList;
		
	}
	
	public Mobile get(int id)
	{
		Mobile mobile;
		ResultSet result;
		PreparedStatement preparedStatement;
		String query = "SELECT * FROM mobiles WHERE id = ?";
		
		connect();
		
		try 
		{
			preparedStatement = con.prepareStatement(query);
			preparedStatement.setInt(1, id);
			result = preparedStatement.executeQuery();
		} 
		catch (SQLException e) 
		{
			System.out.println("[#]Error in getting Result 1 ID");
			//e.printStackTrace();
			return null;
		}
		
		try 
		{
			result.next();
			
			String model = result.getString(2);
			String company = result.getString(3);
			String camera = result.getString(4);
			int price = result.getInt(5);
			
			mobile = new Mobile(id, model, company, camera, price);

		
		} 
		catch (SQLException e) 
		{
			System.out.println("[#]Error in getting Result 2 ID");
			//e.printStackTrace();
			return null;
		}
		
		disconnect();
	
		return mobile;
		
	}
	
	public int insert(Mobile mobile)
	{
		PreparedStatement preparedStatement;
		String query = "INSERT INTO mobiles VALUE(0, ?, ?, ?, ?)";
		int affectedRows;
		
		connect();
		
		try 
		{
			preparedStatement = con.prepareStatement(query);
			preparedStatement.setString(1, mobile.getModel());
			preparedStatement.setString(2, mobile.getCompany());
			preparedStatement.setString(3, mobile.getCamera());
			preparedStatement.setInt(4, mobile.getPrice());
			
			affectedRows = preparedStatement.executeUpdate();
		} 
		catch (SQLException e) 
		{
			System.out.println("[#]Error in Inserting Mobile");
			//e.printStackTrace();
			return -1;
		}
		
		disconnect();
		
		return affectedRows;
		
	}
	
	public int update(Mobile mobile)
	{
		int id = mobile.getId();
		String model = mobile.getModel();
		String company = mobile.getCompany();
		String camera = mobile.getCamera();
		int price = mobile.getPrice();
		
		PreparedStatement preparedStatement;
		int updatedRows;
		String query = "UPDATE mobiles SET model = ?, company = ?, camera = ?, price = ? WHERE id = ?";
		
		//connect();

		try 
		{
			
			System.out.println("[0]");
			connect();
			preparedStatement = con.prepareStatement(query);
			preparedStatement.setString(1, model);
			preparedStatement.setString(2, company);
			preparedStatement.setString(3, camera);
			preparedStatement.setInt(4, price);
			preparedStatement.setInt(5, id);
		} 
		catch (SQLException e) 
		{
			System.out.println("[#]Error in Updating Mobile 1");
			e.printStackTrace();
			return -1;
		}
		
		try 
		{
			updatedRows = preparedStatement.executeUpdate();
		} 
		catch (SQLException e) 
		{
			System.out.println("[#]Error in Updating Mobile 2");
			e.printStackTrace();
			return -1;
		}
		
		disconnect();
		
		return updatedRows;
	}
	
	
	public int delete(int id)
	{
		PreparedStatement preparedStatement;
		String query = "DELETE FROM mobiles WHERE id = ?";
		int affectedRows;
		
		connect();
		
		try 
		{
			preparedStatement = con.prepareStatement(query);
			preparedStatement.setInt(1, id);
			affectedRows = preparedStatement.executeUpdate();
		} 
		catch (SQLException e) 
		{
			System.out.println("[#]Error in Deleting Mobile");
			e.printStackTrace();
			return -1;
		}
		
		disconnect();
		
		return affectedRows;
		
	}
	
	public ArrayList getByCompany(String companyName)
	{
		ArrayList<Mobile> mobilesList = new ArrayList<Mobile>();
		ResultSet result;
		PreparedStatement preparedStatement;
		String query = "SELECT * FROM mobiles WHERE company LIKE ?";
		
		connect();
		
		try 
		{
			preparedStatement = con.prepareStatement(query);
			preparedStatement.setString(1,"%" + companyName + "%");
			result = preparedStatement.executeQuery();
		} 
		catch (SQLException e) 
		{
			System.out.println("[#]Error in getting Result 1 companyName");
			//e.printStackTrace();
			return null;
		}
		
		try 
		{
			while(result.next())
			{
				int id = result.getInt(1);
				String model = result.getString(2);
				String company = result.getString(3);
				String camera = result.getString(4);
				int price = result.getInt(5);
				
				Mobile mobile = new Mobile(id,model,company,camera,price);
				
				mobilesList.add(mobile);
			}
		} 
		catch (SQLException e) 
		{
			System.out.println("[#]Error in getting Result 2 companyName");
			//e.printStackTrace();
			return null;
		}
		
		disconnect();
	
		return mobilesList;
		
	}
	
	public void disconnect()
	{
		try 
		{
			con.close();
		} 
		catch (SQLException e) 
		{
			System.out.println("[#]Error in Closing connection");
			e.printStackTrace();
		}
	}
	
	
}
